package com.firmboy.staticdb;

import java.sql.*;
import java.util.*;

/**
 * 用线程池实现的一个dbHelper
 *
 * @author firmboy
 * @create 2018-04-15 下午10:57
 **/
public class DBPoolHelper {

    private static DBPool pool = null;

    static {
        pool = new DBPool(3,"com/firmboy/staticdb/db.properties");
    }

//    DBPoolHelper(){
//        pool = new DBPool(10,"com/firmboy/staticdb/db.properties");
//    }

    public static boolean insert(String sql,Object[] args){
        Connection conn = pool.getConnection();
        PreparedStatement pst = null;
        boolean result = false;
        try {
            pst = conn.prepareStatement(sql);
            for(int i=0;i<args.length;i++){
                pst.setObject(i+1,args[i]);
            }

            result = pst.execute();

        } catch (SQLException e) {
            try {
                conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
        }finally {
            if(pst!=null){
                try {
                    pst.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            pool.close(conn);
        }
        return result;
    }

    public static void insertBatch(String sql,List<List<Object>> list){
        Connection conn = pool.getConnection();
        try {
            conn.setAutoCommit(false);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        List<Object> obj = list.get(0);
        StringBuffer sb = new StringBuffer();
        sb.append("(");
        for (int i=0;i<obj.size();i++){
            sb.append("?,");
        }
        String str = sb.toString().substring(0,sb.toString().length()-1)+")";

        PreparedStatement pst = null;
        try {
            for(int i=0;i<list.size();i++){
                sql=sql+str+",";
            }
            sql = sql.substring(0,sql.length()-1);

            pst = conn.prepareStatement(sql);
            for(int i=0;i<list.size();i++){
                for(int j=0;j<list.get(i).size();j++){
                    //System.out.println(i*list.get(i).size()+j+1);
                    pst.setObject(i*list.get(i).size()+j+1,list.get(i).get(j));
                }
            }
            pst.execute();
            conn.commit();
        } catch (Exception e) {
            try {
                conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
        }finally {
            if(pst!=null){
                try {
                    pst.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            pool.close(conn);
        }
    }


    public static Map<String,Object> getMapObject(String sql,Object[] args){
        Connection conn = pool.getConnection();
        Map<String, Object> map = new HashMap<>();
        ResultSet result = null;
        try(PreparedStatement pst = conn.prepareStatement(sql);) {
            for(int i=0;i<args.length;i++){
                pst.setObject(i+1,args[i]);
            }

            result = pst.executeQuery();
            List<Map<String, Object>> maps = dealResult(result);
            if(maps!=null&&maps.size()>0){
                map = maps.get(0);
            }
            conn.commit();
        } catch (SQLException e) {
            try {
                conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
        }finally {
            if(result!=null){
                try {
                    result.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            pool.close(conn);
        }
        return map;
    }

    public static List<Map<String,Object>> getListObject(String sql,Object[] args){
        Connection conn = pool.getConnection();
        List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
        PreparedStatement pst = null;
        ResultSet result = null;
        try {
            pst = conn.prepareStatement(sql);
            for(int i=0;i<args.length;i++){
                pst.setObject(i+1,args[i]);
            }

            result = pst.executeQuery();
            list = dealResult(result);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            if(result!=null){
                try {
                    result.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(pst!=null){
                try {
                    pst.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            System.out.println(Thread.currentThread().getName()+"归还连接/"+System.currentTimeMillis());
            pool.close(conn);
            System.out.println(Thread.currentThread().getName()+"归换连接完成/"+System.currentTimeMillis());
        }

        return list;
    }

    private static List<Map<String,Object>> dealResult(ResultSet rs){
        List<Map<String,Object>> result = new ArrayList<Map<String,Object>>();
        Map<String,Object> resultMap = new LinkedHashMap<String,Object>();
        try{
            ResultSetMetaData metaData = rs.getMetaData();
            int count = metaData.getColumnCount();
            Object value = null;
            int columnType = 0;
            String columnName = "";
            Clob clob = null;
            int i;
            int j = 0;
            while (rs.next()) {//循环遍历返回的结果集的每一行
                j++;
                //System.out.println("正在处理第"+j+"条");
                resultMap = new LinkedHashMap<String,Object>();
                for( i = 1; i<=count; i++){
                    columnType = metaData.getColumnType(i);
                    columnName = metaData.getColumnName(i);
                    if(columnType==Types.CLOB){//判断是否是长字符类型
                        clob = rs.getClob(i);
                        if (clob != null){
                            value = clob.getSubString(1, (int) clob.length());
                            clob = null;
                        }else{
                            value = null;
                        }
                    }else{
                        value = rs.getObject(i);
                    }
                    resultMap.put(columnName, value);
                    columnName = null;
                    value = null;
                }
                result.add(resultMap);
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return result;
    }
}
